package com.yuanren.em.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yuanren.em.entity.PointsExchangeRecord;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 积分兑换记录Mapper接口
 */
public interface PointsExchangeRecordMapper extends BaseMapper<PointsExchangeRecord> {

    /**
     * 分页查询兑换记录（带商品和用户信息）
     */
    @Select("SELECT r.*, u.username, u.name as user_name, " +
            "p.name as product_name, p.points_required, p.category, p.product_type " +
            "FROM t_points_exchange_record r " +
            "LEFT JOIN sys_user u ON r.user_id = u.id " +
            "LEFT JOIN t_points_exchange_product p ON r.product_id = p.id " +
            "WHERE (#{userId} IS NULL OR r.user_id = #{userId}) " +
            "AND (#{productId} IS NULL OR r.product_id = #{productId}) " +
            "AND (#{status} IS NULL OR r.status = #{status}) " +
            "AND (#{category} IS NULL OR p.category = #{category}) " +
            "ORDER BY r.exchange_date DESC")
    IPage<PointsExchangeRecord> selectExchangeRecordPageWithInfo(Page<PointsExchangeRecord> page,
                                                                @Param("userId") Long userId,
                                                                @Param("productId") Long productId,
                                                                @Param("status") String status,
                                                                @Param("category") String category);

    /**
     * 根据用户ID查询兑换记录
     */
    @Select("SELECT r.*, u.username, u.name as user_name, " +
            "p.name as product_name, p.points_required, p.category " +
            "FROM t_points_exchange_record r " +
            "LEFT JOIN sys_user u ON r.user_id = u.id " +
            "LEFT JOIN t_points_exchange_product p ON r.product_id = p.id " +
            "WHERE r.user_id = #{userId} " +
            "ORDER BY r.exchange_date DESC " +
            "LIMIT #{limit}")
    List<PointsExchangeRecord> selectByUserId(@Param("userId") Long userId, @Param("limit") Integer limit);

    /**
     * 根据兑换码查询记录
     */
    @Select("SELECT r.*, u.username, u.name as user_name, " +
            "p.name as product_name, p.category " +
            "FROM t_points_exchange_record r " +
            "LEFT JOIN sys_user u ON r.user_id = u.id " +
            "LEFT JOIN t_points_exchange_product p ON r.product_id = p.id " +
            "WHERE r.exchange_code = #{exchangeCode}")
    PointsExchangeRecord selectByExchangeCode(@Param("exchangeCode") String exchangeCode);

    /**
     * 统计用户兑换情况
     */
    @Select("SELECT " +
            "COUNT(*) as total_exchanges, " +
            "SUM(points_spent) as total_points_spent, " +
            "SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) as completed_exchanges, " +
            "SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) as pending_exchanges " +
            "FROM t_points_exchange_record " +
            "WHERE user_id = #{userId}")
    Map<String, Object> selectUserExchangeStats(@Param("userId") Long userId);

    /**
     * 统计总体兑换情况
     */
    @Select("SELECT " +
            "COUNT(*) as total_exchanges, " +
            "COUNT(DISTINCT user_id) as unique_users, " +
            "SUM(points_spent) as total_points_spent, " +
            "AVG(points_spent) as avg_points_spent, " +
            "SUM(CASE WHEN status = 'COMPLETED' THEN 1 ELSE 0 END) as completed_exchanges " +
            "FROM t_points_exchange_record " +
            "WHERE DATE(exchange_date) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)")
    Map<String, Object> selectExchangeStats();

    /**
     * 根据商品统计兑换情况
     */
    @Select("SELECT p.name as product_name, p.category, " +
            "COUNT(*) as exchange_count, " +
            "SUM(r.points_spent) as total_points, " +
            "SUM(r.quantity) as total_quantity " +
            "FROM t_points_exchange_record r " +
            "LEFT JOIN t_points_exchange_product p ON r.product_id = p.id " +
            "WHERE r.status = 'COMPLETED' " +
            "AND DATE(r.exchange_date) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "GROUP BY r.product_id, p.name, p.category " +
            "ORDER BY exchange_count DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> selectPopularProducts(@Param("limit") Integer limit);

    /**
     * 查询即将过期的兑换记录
     */
    @Select("SELECT r.*, p.name as product_name, u.username, u.name as user_name " +
            "FROM t_points_exchange_record r " +
            "LEFT JOIN t_points_exchange_product p ON r.product_id = p.id " +
            "LEFT JOIN sys_user u ON r.user_id = u.id " +
            "WHERE r.status = 'COMPLETED' " +
            "AND r.expire_date IS NOT NULL " +
            "AND r.expire_date <= DATE_ADD(NOW(), INTERVAL #{days} DAY) " +
            "AND r.used_date IS NULL " +
            "ORDER BY r.expire_date ASC")
    List<PointsExchangeRecord> selectExpiringRecords(@Param("days") Integer days);

    /**
     * 根据用户ID和商品ID查询兑换记录
     */
    @Select("SELECT COUNT(*) FROM t_points_exchange_record " +
            "WHERE user_id = #{userId} AND product_id = #{productId} " +
            "AND status IN ('PENDING', 'COMPLETED')")
    Integer countByUserIdAndProductId(@Param("userId") Long userId, @Param("productId") Long productId);
} 